<?php

class Application_Model_DbTable_RankingPrice extends Zend_Db_Table_Abstract {

    protected $_name = 'RankingPrice';

    public function getRank4ED($mainCat, $pid) {
        $rank = 0;
        $k = 0;
        $rankRule = new Application_Model_DbTable_RankingRule();
        $where = $rankRule->getAdapter()->quoteInto("MainCat = ? ", $mainCat);
        $records = $rankRule->fetchAll($where);
        foreach ($records->toArray() as $record) {
            $rank = $rank + $record['SubcatRule'] * $this->getSubRank($record['Subcat'], $pid);
            $k++;
        }
        if ($k > 0) {
            return $rank / $k;
        } else {
            return 0;
        }
    }

    public function getSubRank($subCat, $pid) {
        $a = 0;
        $i = 0;
        $rankRule = new Application_Model_DbTable_RankingRule();
        $where = $rankRule->getAdapter()->quoteInto("Subcat = ? ", $subCat);
        $records = $rankRule->fetchAll($where);
        foreach ($records->toArray() as $record) {
            $sql = "select SupplyID from ProductSupply where ProductID = $pid AND SupplyTypeID = " . intval($record['SupplyType']);
            $sIDs = $this->getAdapter()->fetchAssoc($sql);
            foreach ($sIDs as $sID) {
                $rankRow = $this->getAdapter()->fetchAssoc("select RankingGrade from SupplyRanking where SupplyID = ?", array(intval($sID['SupplyID'])));
                $rankRow = array_values($rankRow);
                $a = $a + $record['Rule'] * $rankRow[0]['RankingGrade'];
                $i++;
            }
        }
        if ($i != 0) {
            return $a / $i;
        } else {
            return 0;
        }
    }

    public function getPraticalRank($pid) {
        $mainCat = 'PRACTICAL';
        $rank = 0;
        $rankRule = new Application_Model_DbTable_RankingRule();
        $where = $rankRule->getAdapter()->quoteInto("MainCat = ? ", $mainCat);
        $records = $rankRule->fetchAll($where);
        foreach ($records->toArray() as $record) {
            if (strlen(strstr($record['Subcat'], 'Inner')) > 0) {
                $innerWeight = $record['SubcatRule'];
            };
            if (strlen(strstr($record['Subcat'], 'Outside')) > 0) {
                $oursideWeight = $record['SubcatRule'];
            };
            if (strlen(strstr($record['Subcat'], 'Expandable')) > 0) {
                $expanWeight = $record['SubcatRule'];
            };
        }
        
        $newsql = "select t.Name,s.RankingGrade from ProductSupply p, SupplyType t ,SupplyRanking s where p.SupplyID =s.SupplyID and p.SupplyTypeID=t.ID and t.ID in (21,22,23) and p.ProductID = ? ";
        //$sql = "select InnerOrg,OuterOrg,Expandable from Product where ID = ?";
        
        $result = $this->getAdapter()->fetchAssoc($newsql, array($pid));
        $result = array_values($result);
        $innerOrg = 0;
        $outOrg = 0;
        $expand = 0;
        if(count($result)>0){            
            foreach ($result as $value) {
                if($value['Name'] == 'Inner organization'){
                    $innerOrg = $value['RankingGrade'];
                }
                if($value['Name'] == 'Outside organization'){
                     $outOrg = $value['RankingGrade'];
                }
                if($value['Name'] == 'Expandable'){
                     $expand = $value['RankingGrade'];
                }
            }
        $rank = $innerOrg*$innerWeight+$outOrg*$oursideWeight + $expand* $expanWeight;    
       // $rank = $result[0]['InnerOrg']*$innerWeight+$result[0]['OuterOrg']*$oursideWeight + $result[0]['Expandable']* $expanWeight;
        }
        return $rank;
    }

    
    
    public function getWeightRank($pid){
        $sql = "select ProductTypeID,SetWeight,UnitWeight from Product where ID = ?";        
        $result = $this->getAdapter()->fetchAssoc($sql, array($pid));
        $result = array_values($result);
        $rank = 0;
        if(count($result)==0){
            return $rank;
        }
        $wsql = "select t.TypeProduct,r.MinWeight,r.MaxWeight from RankingWeight r join TypeProduct t on r.TypeProductID = t.ID where r.TypeProductID = ?";
        $rankWeight = $this->getAdapter()->fetchAssoc($wsql, array($result[0]['ProductTypeID']));
        $rankWeight = array_values($rankWeight);
        if(count($rankWeight)>0){
        if($rankWeight[0]['TypeProduct']=='Trolley Case'){
         $rank = (($result[0]['SetWeight'] - $rankWeight[0]['MinWeight'])/($rankWeight[0]['MaxWeight'] - $rankWeight[0]['MinWeight']))*5 +1;
        }else{
          $rank = (($result[0]['UnitWeight'] - $rankWeight[0]['MinWeight'])/($rankWeight[0]['MaxWeight'] - $rankWeight[0]['MinWeight']))*5 +1;  
        }
        }
        return $rank;        
    }
    
    public function getPriceRank($pid){
        $sql = "select ProductTypeID,Price from Product where ID = ?";        
        $result = $this->getAdapter()->fetchAssoc($sql, array($pid));
        $result = array_values($result);
        $rank = 0;
        if(count($result)>0){
        $wsql = "select ID,MinPrice,MaxPrice from RankingPrice  where TypeProductID = ?";
        $rankPrice = $this->getAdapter()->fetchAssoc($wsql, array($result[0]['ProductTypeID']));
        $rankPrice = array_values($rankPrice);
        if(count($rankPrice)){
        $rank = (($result[0]['Price']-$rankPrice[0]['MinPrice'])/($rankPrice[0]['MaxPrice']-$rankPrice[0]['MinPrice']))*5 + 1;
        }        
        }
        return $rank;
    }
}